\section*{Problem 1}

	In this exercise, we want to add a constraint called \textit{adv} on the \verb?advisor? table. That constraint is satisfied if and only if at least one of the following conditions is met.
	\begin{enumerate}
		\item The advisor is in the same department as the student.
		\item The \verb?dept_name? of the advisor is \verb?NULL?.
		\item The \verb?dept_name? of the student is \verb?NULL?.
		\item The advisor of the student is \verb?NULL?.
	\end{enumerate}
	
	To add such a constraint, we have to write a stored function. That function must 
have the following header : \verb?check_advisor(student_id VARCHAR(5))? and must return an integer value (interpreted as a boolean). If the constraint is satisfied, the function will return 1, 0 otherwise.\\

	Then we will use that function to create three triggers to check if the constraint is preserved.

	\subsection*{User manual}
	
		\subsubsection*{Adding check\_advisor() in the database}
	
			To add the stored function to the database, you have to import the script \verb?assignment_problem1.sql? into a database which has \verb?university? schema.
			Here is the command to do this (considering you are already in the directory containing \verb?assignment_problem1.sql?) :\\
			\verb?\i assignment_problem1.sql?.
	
		\subsubsection*{Using the check\_advisor() function}
	
			After importing the function, you can use it just by using the following command line : \verb?SELECT check_advisor(<student_id>);?.\\
			\textbf{Parameter :}
			\begin{itemize}
				\item student\_id : student's id for who we want to check if the constraint is satisfied.
			\end{itemize}
		
		\subsubsection*{Triggers on advisor, student and instructor}
	
			When importing the script, triggers will be created. You can't use it directly but when an insert or update is made on \verb?advisor? it will check if \textit{adv} constraint is preserved. It will do the same but only for update query on \verb?student? and \verb?instructor?.
		
	\subsection*{What do the programs }
	
		\subsubsection*{Principle of a trigger}
		
			\begin{figure}[H]
				\centering
				\includegraphics[scale=0.6]{img/seq_trigger.png}
				\caption{Principle of a trigger}
				\label{seq_trigger}
			\end{figure}
			
			Figure \ref{seq_trigger} describes the  principle of a trigger which operates after the query. As we can see, when an user does an operation on the database, the trigger is executed and depending of its return value, the query is either validated or is rollbacked.\\
			
			Next sections will describe the comportment of our triggers.
			
			\subsubsection*{Trigger on advisor}
			
				The trigger on \verb?advisor? is executed if an insert or an update query is made on \verb?advisor? table. It checks if \textit{adv} is preserved after user's query and if this is not the case, an exception is raised and a rollback occurs. There are two conditions which can affect \textit{adv} constraint :
				\begin{itemize}
					\item New student id is \verb?NULL?.
					\item Student's department doesn't match instructor's department.
				\end{itemize}
				\vspace{0.7cm}
				 \textit{
					All the following examples uses the query described below to get the state of the database} \begin{verbatim}
					select s_ID, i_ID, student.ID as student_ID, 
					    student.dept_name as student_dept_name, 
					    instructor.ID as instructor_ID, instructor.dept_name as instructor_dept_name  
					from advisor 
					join student on s_id = student.ID 
					join instructor on i_ID = instructor.ID;
					\end{verbatim}
					
				\paragraph*{Example : invalid update on advisor table\\}
					Here is the current data in database :
					
					
					\begin{verbatim}
						 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 00128 | 45565 | 00128      | Comp. Sci.        | 45565         | Comp. Sci.
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
					\end{verbatim}
					
					We will update student 00128 and set his advisor to instructor 76766 (of course it must fail).
					
					\begin{verbatim}
						UPDATE advisor set i_id = '76766' WHERE s_id = '00128';
					\end{verbatim}
					
					The trigger is executed and display the following error :\\
					\verb?ERROR:  Student department name must match Advisor department name?\\
					
					And here is the data after the trigger execution :
					\begin{verbatim}
						 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 00128 | 45565 | 00128      | Comp. Sci.        | 45565         | Comp. Sci.
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
					\end{verbatim}
					
					
	\paragraph*{Example : valid update on advisor table\\}
					Here is the current data in database :
				
					
					\begin{verbatim}
						 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 00128 | 45565 | 00128      | Comp. Sci.        | 45565         | Comp. Sci.
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
					\end{verbatim}
					
					We will update student 00128 and set his advisor to instructor 10101. This time it should work, as this instructor is in the same department.
					
					\begin{verbatim}
						UPDATE advisor set i_id = '10101' WHERE s_id = '00128';
					\end{verbatim}
					
					The trigger is executed, and return the following result :\\
					\verb?NOTICE:  Request executed normally? \\
					\verb?UPDATE 1?

					
					And here is the data after the trigger execution :
					\begin{verbatim}
 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      | Comp. Sci.        | 10101         | Comp. Sci.
					\end{verbatim}
									
		
			\subsubsection*{Trigger on student}
			
				The trigger on \verb?student? is executed if an update occurs on that table. Because the only update which can affect \textit{adv} constraint is an update of \verb?dept_name? field, we firstly check if such a change occurs. If not the query is executed normally. Otherwise we check if departments name are the same. If they are equals, update query is executed normally,  if not, an exception is raised indicating for which tuple \textit{adv} is not preserved.
								
			\paragraph*{Example : invalid update on student table\\}
					Here is the current data in database :
					
					\begin{verbatim}
						  s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      | Comp. Sci.        | 10101         | Comp. Sci.
					\end{verbatim}
					
					We will update student 00128 and set his department to 'Music'.
					
					\begin{verbatim}
						UPDATE student set dept_name = 'Music' WHERE ID = '00128';
					\end{verbatim}
					
					The trigger is executed and display the following error :\\
					\verb?ERROR:  Constraint violated for student 00128 and instructor 10101?\\
					
					And here is the data after the trigger execution :
					\begin{verbatim}
					 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      | Comp. Sci.        | 10101         | Comp. Sci.
					\end{verbatim}
					
					
	\paragraph*{Example : valid update on student table\\}
					Here is the current data in database :

					\begin{verbatim}
 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      | Comp. Sci.        | 10101         | Comp. Sci.
					\end{verbatim}
					
					We will update student 00128 and set his department to \verb?NULL?. 
					
					\begin{verbatim}
								UPDATE student set dept_name = NULL WHERE ID = '00128';
					\end{verbatim}
					
					The trigger is executed, and return the following result :\\
					\verb?NOTICE Request executed normally? \\
					\verb?UPDATE 1?

					
					And here is the data after the trigger execution :
					\begin{verbatim}
 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      |                   | 10101         | Comp. Sci.
					\end{verbatim}
			 \subsubsection*{Trigger on instructor}
			 
			 	The trigger on \verb?instructor? is executed if an update occurs on that table. Like for the trigger on \verb?student?, firstly we check if the update concerns \verb?dept_name? field. If not, update is executed, otherwise, we check if \textit{adv} constraint is preserved for each student having this instructor as an advisor. If for only one pair constraint is violated, an exception is raised and a rollback occurs. Else, the update is executed normally.
			 	
			\paragraph*{Example : invalid update on instructor table\\}
					Here is the current data in database :

					\begin{verbatim}
 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      |                   | 10101         | Comp. Sci.

					\end{verbatim}
					
					We will update instructor 10101 and set his department to 'Music'.
					
					\begin{verbatim}
						UPDATE instructor set dept_name = 'Music' WHERE ID = '10101';
					\end{verbatim}
					
					The trigger is executed and display the following error :\\
					\verb?ERROR:  Constraint violated for student 12345?\\
					Note that it should have work for student 00128, but as student 12345 causes an exception, the rollback is executed. 
					
					And here is the data after the trigger execution :
					\begin{verbatim}
 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      |                   | 10101         | Comp. Sci.

					\end{verbatim}
					
					
	\paragraph*{Example : valid update on instructor table\\}
					Here is the current data in database :

					\begin{verbatim}
 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | 
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      |                   | 10101         | 


					\end{verbatim}
					
					We will update instructor 10101 and set his department to 'Comp. Sci.'. 
					
					\begin{verbatim}
								UPDATE instructor set dept_name = 'Comp. Sci.' WHERE ID = '10101';
					\end{verbatim}
					
					The trigger is executed, and return the following result :\\
					\verb?NOTICE Request executed normally? \\
					\verb?UPDATE 1?

					
					And here is the data after the trigger execution :
					\begin{verbatim}
 s_id  | i_id  | student_id | student_dept_name | instructor_id | instructor_dept_name 
-------+-------+------------+-------------------+---------------+----------------------
 12345 | 10101 | 12345      | Comp. Sci.        | 10101         | Comp. Sci.
 23121 | 76543 | 23121      | Finance           | 76543         | Finance
 44553 | 22222 | 44553      | Physics           | 22222         | Physics
 45678 | 22222 | 45678      | Physics           | 22222         | Physics
 76543 | 45565 | 76543      | Comp. Sci.        | 45565         | Comp. Sci.
 76653 | 98345 | 76653      | Elec. Eng.        | 98345         | Elec. Eng.
 98765 | 98345 | 98765      | Elec. Eng.        | 98345         | Elec. Eng.
 98988 | 76766 | 98988      | Biology           | 76766         | Biology
 00128 | 10101 | 00128      |                   | 10101         | Comp. Sci.
					\end{verbatim}			
					 	
	\subsection*{Tests}
	
		Results of provided tests could be found in appendix.
		
\section*{Problem 2}

This is the result of the rollup executed on the example database for \verb?sales?, obtained with the following query  : \\
\verb?SELECT * FROM sales_rollup;?

\begin{verbatim}
 clothes_size | color  | menswear | womenswear | total 
--------------+--------+----------+------------+-------
 medium       | white  |        1 |          8 |     9
 small        | dark   |       16 |          2 |    18
 small        | white  |       20 |          2 |    22
 medium       | dark   |        6 |         11 |    17
 medium       | pastel |        1 |         12 |    13
 small        | pastel |        5 |         15 |    20
 large        | white  |       12 |          3 |    15
 large        | dark   |        6 |         13 |    19
 large        | pastel |        3 |         18 |    21
 all          | white  |       33 |         13 |    46
 all          | dark   |       28 |         26 |    54
 all          | pastel |        9 |         45 |    54
 large        | all    |       21 |         34 |    55
 medium       | all    |        8 |         31 |    39
 small        | all    |       41 |         19 |    60
 all          | all    |       70 |         84 |   154
(16 lignes)
\end{verbatim}